package com.zhou.dao.impl;

import com.zhou.bean.FastMan;
import com.zhou.bean.User;
import com.zhou.bean.UserConut;
import com.zhou.dao.UserDao;
import com.zhou.utils.DateFormatUtil;
import com.zhou.utils.DbUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class UserDaoImpl extends DbUtils implements UserDao {

    /**
     * 查询总的用户数和日注册用户数
     * @return
     */

    public List findAllAndAllDay(){
        String sql = "SELECT count(*) total,COUNT(to_days(createtime)=to_days(now()) or null) day FROM user";
        ResultSet query = query(sql, null);
        List list = new ArrayList<>();
        try {
            while (query.next()){
                list.add(query.getInt("total"));
                list.add(query.getInt("day"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }
    /**
     * 查询用户列表密码记得隐藏
     *
     * @return
     */
    @Override
    public List<User> selectAll(Integer pageIndex,Integer pageSize) {
        String sql = "select id,username,userphone,idcard,createtime,logintime from user limit ?,? ";
        List param = new ArrayList();
        param.add(pageIndex);
        param.add(pageSize);
        List<User> userList = new ArrayList<>();
        ResultSet query = query(sql, param);
        try {
            while (query.next()){
                User user = new User();
                user.setId(query.getInt("id"));
                user.setUsername(query.getString("username"));
                user.setUserPhone(query.getString("userphone"));
                user.setIdCard(query.getString("idcard"));
                user.setCreateTime(DateFormatUtil.format(query.getTimestamp("createtime")));
                user.setLoginTime(DateFormatUtil.format(query.getTimestamp("logintime")));
                userList.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return userList;
    }

    /**
     * 查询总条数
     *
     * @return
     */
    @Override
    public int total() {
        int total = 0;
        String sql = "select count(*) from user";
        ResultSet query = query(sql, null);
        try {
            while (query.next()){
                total = query.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    /**
     * 增加用户
     *
     * @param user
     * @return
     */
    @Override
    public int addUser(User user) {
        String sql  = "insert into user(username,userphone,idcard,password,createtime) values(?,?,?,?,?)";
        List list = new ArrayList();
        list.add(user.getUsername());
        list.add(user.getUserPhone());
        list.add(user.getIdCard());
        list.add(user.getPassword());
        list.add(new Date());
        int update = 0;
        try {
            update = update(sql, list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    /**
     * 通过手机号查询用户
     *
     * @param userPhone 用户手机号
     * @return
     */
    @Override
    public User findByUserPhone(String userPhone) {
        String sql  = "select id,username,userphone,idcard,password from user where userphone=?";
        List param = new ArrayList();
        param.add(userPhone);
        ResultSet query = query(sql, param);
        User user = new User();
        try {
            while (query.next()){
                user.setId(query.getInt("id"));
                user.setUsername(query.getString("username"));
                user.setUserPhone(query.getString("userphone"));
                user.setIdCard(query.getString("idcard"));
                user.setPassword(query.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return user;
    }

    /**
     * 删除用户
     *
     * @param userPhone 用户手机号
     * @return
     */
    @Override
    public int deleteByUserPhone(String userPhone) {
        String sql = " delete from user where userphone = ?";
        List param = new ArrayList();
        param.add(userPhone);
        int update = 0;
        try {
            update = update(sql, param);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    /**
     * 更新用户
     *
     * @param id   用户id
     * @param user 新用户
     * @return
     */
    @Override
    public int updateUser(int id, User user) {
        String sql = "update user set username=?,userphone=?,idcard=?,password=? where id=?";
        List param = new ArrayList();
        param.add(user.getUsername());
        param.add(user.getUserPhone());
        param.add(user.getIdCard());
        param.add(user.getPassword());
        param.add(user.getId());
        int update = 0;
        try {
            update = update(sql, param);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    /**
     * 设置登录时间
     * @param id 登录的用户id
     * @return
     */
    public int setLoginTime(int id){
        String sql  = "update user set logintime=? where id = ?";
        List list = new ArrayList();
        list.add(new Date());
        list.add(id);
        int update = 0;
        try {
            update = update(sql, list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
    /**
     * 查询所有用户所拥有的快件数 降序排序
     */
    @Override
    public List<UserConut> selectUserCount() {
        String sql = "SELECT username,count(*) ecount\n" +
                "from express where 1=1 GROUP BY username\n" +
                "ORDER BY ecount desc";
        List<UserConut> userConutList = new ArrayList<>();
        ResultSet query = query(sql, null);
        try {
            while (query.next()){
                UserConut userConut = new UserConut();
                userConut.setUsername(query.getString("username"));
                userConut.setTotal(query.getInt("ecount"));
                userConutList.add(userConut);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return userConutList;
    }
}
